import { Pool as MySQLPool, ResultSetHeader, RowDataPacket } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import { DatabaseType } from '../types/index.js';
import { GlobalContext } from './context.js';

/**
 * 通用数据库操作接口
 * 定义了基本的CRUD操作方法
 */
export interface DatabaseOperations<T extends Record<string, unknown> = Record<string, unknown>> {
  /**
   * 创建记录
   * @param data 要创建的数据
   * @returns 创建的记录ID
   */
  create(data: Omit<T, 'id'>): Promise<number>;

  /**
   * 根据ID获取记录
   * @param id 记录ID
   * @returns 记录数据或null
   */
  getById(id: number): Promise<T | null>;

  /**
   * 更新记录
   * @param id 记录ID
   * @param data 要更新的数据
   * @returns 是否更新成功
   */
  update(id: number, data: Partial<T>): Promise<boolean>;

  /**
   * 删除记录
   * @param id 记录ID
   * @returns 是否删除成功
   */
  delete(id: number): Promise<boolean>;

  /**
   * 分页查询记录列表
   * @param page 页码
   * @param pageSize 每页数量
   * @param conditions 查询条件
   * @returns 分页结果
   */
  list(
    page: number,
    pageSize: number,
    conditions?: Partial<T>
  ): Promise<{
    list: T[];
    total: number;
    page: number;
    pageSize: number;
  }>;
}

/**
 * 数据库操作工厂类
 * 根据当前数据库类型创建相应的操作实例
 */
export class DatabaseOperationsFactory {
  /**
   * 根据当前数据库类型创建对应的数据库操作实例
   * @param MySQLClass MySQL操作类
   * @param PostgreSQLClass PostgreSQL操作类
   * @returns 数据库操作实例
   */
  static create<T extends DatabaseOperations>(
    MySQLClass: new (pool: MySQLPool) => T,
    PostgreSQLClass: new (pool: PgPool) => T
  ): T {
    const dbType = GlobalContext.getDbType();
    const pool = GlobalContext.getDbPool();

    if (dbType === DatabaseType.MYSQL) {
      return new MySQLClass(pool as MySQLPool);
    } else {
      return new PostgreSQLClass(pool as PgPool);
    }
  }
}

/**
 * MySQL数据库操作基类
 * 提供MySQL特定的通用操作方法
 */
export abstract class BaseMySQLOperations<
  T extends Record<string, unknown> = Record<string, unknown>,
> implements DatabaseOperations<T>
{
  constructor(protected pool: MySQLPool) {}

  abstract create(data: Omit<T, 'id'>): Promise<number>;
  abstract getById(id: number): Promise<T | null>;
  abstract update(id: number, data: Partial<T>): Promise<boolean>;
  abstract delete(id: number): Promise<boolean>;
  abstract list(
    page: number,
    pageSize: number,
    conditions?: Partial<T>
  ): Promise<{
    list: T[];
    total: number;
    page: number;
    pageSize: number;
  }>;

  /**
   * 执行查询并返回单个结果
   * @param sql SQL语句
   * @param params 参数
   * @returns 查询结果或null
   */
  protected async queryOne<R = T>(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<R | null> {
    const [rows] = await this.pool.execute<RowDataPacket[]>(sql, params);
    return rows.length > 0 ? (rows[0] as R) : null;
  }

  /**
   * 执行查询并返回多个结果
   * @param sql SQL语句
   * @param params 参数
   * @returns 查询结果数组
   */
  protected async queryMany<R = T>(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<R[]> {
    const [rows] = await this.pool.execute<RowDataPacket[]>(sql, params);
    return rows as R[];
  }

  /**
   * 执行插入操作
   * @param sql SQL语句
   * @param params 参数
   * @returns 插入的记录ID
   */
  protected async executeInsert(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<number> {
    const [result] = await this.pool.execute<ResultSetHeader>(sql, params);
    return result.insertId;
  }

  /**
   * 执行更新或删除操作
   * @param sql SQL语句
   * @param params 参数
   * @returns 受影响的行数
   */
  protected async executeUpdate(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<number> {
    const [result] = await this.pool.execute<ResultSetHeader>(sql, params);
    return result.affectedRows;
  }
}

/**
 * PostgreSQL数据库操作基类
 * 提供PostgreSQL特定的通用操作方法
 */
export abstract class BasePostgreSQLOperations<
  T extends Record<string, unknown> = Record<string, unknown>,
> implements DatabaseOperations<T>
{
  constructor(protected pool: PgPool) {}

  abstract create(data: Omit<T, 'id'>): Promise<number>;
  abstract getById(id: number): Promise<T | null>;
  abstract update(id: number, data: Partial<T>): Promise<boolean>;
  abstract delete(id: number): Promise<boolean>;
  abstract list(
    page: number,
    pageSize: number,
    conditions?: Partial<T>
  ): Promise<{
    list: T[];
    total: number;
    page: number;
    pageSize: number;
  }>;

  /**
   * 执行查询并返回单个结果
   * @param sql SQL语句
   * @param params 参数
   * @returns 查询结果或null
   */
  protected async queryOne<R = T>(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<R | null> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(sql, params);
      return result.rows.length > 0 ? (result.rows[0] as R) : null;
    } finally {
      client.release();
    }
  }

  /**
   * 执行查询并返回多个结果
   * @param sql SQL语句
   * @param params 参数
   * @returns 查询结果数组
   */
  protected async queryMany<R = T>(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<R[]> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(sql, params);
      return result.rows as R[];
    } finally {
      client.release();
    }
  }

  /**
   * 执行插入操作
   * @param sql SQL语句
   * @param params 参数
   * @returns 插入的记录ID
   */
  protected async executeInsert(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<number> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(sql, params);
      return result.rows[0]?.id || 0;
    } finally {
      client.release();
    }
  }

  /**
   * 执行更新或删除操作
   * @param sql SQL语句
   * @param params 参数
   * @returns 受影响的行数
   */
  protected async executeUpdate(
    sql: string,
    params: (string | number | boolean | null)[] = []
  ): Promise<number> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(sql, params);
      return result.rowCount || 0;
    } finally {
      client.release();
    }
  }
}
